﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace HensonOnline
    Partial Public Class Family

        Dim connString As String = ConfigurationManager.ConnectionStrings("Henson Family").ConnectionString
        Dim conn As New SqlConnection(connString)
        Private m_Errors As List(Of System.Exception)
        Public ReadOnly Property Errors() As List(Of System.Exception)
            Get
                Return Me.m_Errors
            End Get
        End Property


        Public Function dtFamilies() As DataTable
            Dim dt As New DataTable("Families")

            Dim dcFamilyId As New DataColumn("FamilyId", GetType(Integer))
            Dim dcPFamilyId As New DataColumn("PFamilyId", GetType(Integer))
            Dim dcFamilyName As New DataColumn("FamilyName", GetType(String))
            Dim dcMother As New DataColumn("Mother", GetType(String))
            Dim dcFather As New DataColumn("Father", GetType(String))
            Dim dcAddress As New DataColumn("Address", GetType(String))
            Dim dcCity As New DataColumn("City", GetType(String))
            Dim dcState As New DataColumn("State", GetType(String))
            Dim dcZip As New DataColumn("Zip", GetType(String))
            Dim dcPhone1 As New DataColumn("Phone1", GetType(String))
            Dim dcPhone2 As New DataColumn("Phone2", GetType(String))
            Dim dcMemberCount As New DataColumn("MemberCount", GetType(Integer))

            dt.Columns.Add(dcFamilyId)
            dt.Columns.Add(dcPFamilyId)
            dt.Columns.Add(dcFamilyName)
            dt.Columns.Add(dcMother)
            dt.Columns.Add(dcFather)
            dt.Columns.Add(dcAddress)
            dt.Columns.Add(dcCity)
            dt.Columns.Add(dcState)
            dt.Columns.Add(dcZip)
            dt.Columns.Add(dcPhone1)
            dt.Columns.Add(dcPhone2)
            dt.Columns.Add(dcMemberCount)

            Return dt
        End Function

        Public Function GetFamily(ByVal FamilyId As Integer) As ArrayList
            Dim sql As String = "[dbo].[sp_getFamilyById]"
            Dim type As CommandType = CommandType.StoredProcedure
            Dim parameters As List(Of System.Data.SqlClient.SqlParameter) = New List(Of System.Data.SqlClient.SqlParameter)
            parameters.Add(New System.Data.SqlClient.SqlParameter("@FamilyId", FamilyId))

            Dim propertyList As ArrayList = SelectSingle(sql, type, parameters.ToArray, 12)

            _FamilyId = propertyList(0)
            _PFamilyId = propertyList(1)
            _FamilyName = propertyList(2)
            _Mother = propertyList(3)
            _Father = propertyList(4)
            _Address = propertyList(5)
            _City = propertyList(6)
            _State = propertyList(7)
            _Zip = propertyList(8)
            _Phone1 = propertyList(9)
            _Phone2 = propertyList(10)
            _MemberCount = propertyList(11)

            Return propertyList
        End Function

        Public Function SelectSingle(ByVal sql As String, ByVal commandType As CommandType, ByVal parameters() As SqlParameter, ByVal rowCount As Integer) As ArrayList
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            Dim propertyList As New ArrayList
            comm.CommandType = commandType
            If ((Not (parameters) Is Nothing) _
               AndAlso (parameters.Length > 0)) Then
                comm.Parameters.AddRange(parameters)
            End If
            Try
                If (conn.State <> ConnectionState.Open) Then
                    conn.Open()
                End If
                Dim dr As SqlDataReader = comm.ExecuteReader
                Dim count As Integer = 0
                If dr.Read Then
                    For i As Integer = 0 To rowCount - 1
                        propertyList.Add(fn.CheckNull(dr(i)))
                    Next i
                End If
                dr.Close()
            Catch ex As System.Exception
                Dim newEx As System.Exception = New System.Exception("From SelectSingle. See inner exception.", ex)
                Me.Errors.Add(newEx)
            Finally
                If (conn.State <> ConnectionState.Closed) Then
                    conn.Close()
                End If
                comm.Dispose()
            End Try
            Return propertyList
        End Function

        Public Function SelectList(ByVal sql As String, ByVal commandType As CommandType, ByVal parameters() As SqlParameter, ByVal datatable As DataTable) As DataTable
            Dim objs As List(Of Family) = New List(Of Family)
            Dim dt As DataTable = datatable
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            comm.CommandType = commandType
            If ((Not (parameters) Is Nothing) _
               AndAlso (parameters.Length > 0)) Then
                comm.Parameters.AddRange(parameters)
            End If
            Try
                If (conn.State <> ConnectionState.Open) Then
                    conn.Open()
                End If
                Dim dr As SqlDataReader = comm.ExecuteReader
                Dim row As DataRow
                Do While dr.Read
                    row = dt.NewRow
                    For i As Integer = 0 To dt.Columns.Count - 1
                        row(i) = dr(i)
                    Next i
                    dt.Rows.Add(row)
                Loop
                dr.Close()
            Catch ex As System.Exception
                Dim newEx As System.Exception = New System.Exception("From SelectList. See inner exception.", ex)
                Me.Errors.Add(newEx)
            Finally
                If (conn.State <> ConnectionState.Closed) Then
                    conn.Close()
                End If
                comm.Dispose()
            End Try
            Return dt
        End Function


    End Class

    Public Class DataAccess

        Dim connString As String = ConfigurationManager.ConnectionStrings("Henson Family").ConnectionString
        Dim conn As New SqlConnection(connString)
        Private m_Errors As List(Of System.Exception)
        Public ReadOnly Property Errors() As List(Of System.Exception)
            Get
                Return Me.m_Errors
            End Get
        End Property

        'Public Function GetFamily(ByVal FamilyId As Integer) As ArrayList
        '    Dim sql As String = "[dbo].[sp_getFamilyById]"
        '    Dim type As CommandType = CommandType.StoredProcedure
        '    Dim parameters As List(Of System.Data.SqlClient.SqlParameter) = New List(Of System.Data.SqlClient.SqlParameter)
        '    parameters.Add(New System.Data.SqlClient.SqlParameter("@FamilyId", FamilyId))

        '    Dim propertyList As ArrayList = SelectSingle(sql, type, parameters.ToArray, 12)

        '    _FamilyId = propertyList(0)
        '    _PFamilyId = propertyList(1)
        '    _FamilyName = propertyList(2)
        '    _Mother = propertyList(3)
        '    _Father = propertyList(4)
        '    _Address = propertyList(5)
        '    _City = propertyList(6)
        '    _State = propertyList(7)
        '    _Zip = propertyList(8)
        '    _Phone1 = propertyList(9)
        '    _Phone2 = propertyList(10)
        '    _MemberCount = propertyList(11)

        '    Return propertyList
        'End Function

        Public Function SelectSingle(ByVal sql As String, ByVal commandType As CommandType, ByVal parameters() As SqlParameter, ByVal rowCount As Integer) As ArrayList
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            Dim propertyList As New ArrayList
            comm.CommandType = commandType
            If ((Not (parameters) Is Nothing) _
               AndAlso (parameters.Length > 0)) Then
                comm.Parameters.AddRange(parameters)
            End If
            Try
                If (conn.State <> ConnectionState.Open) Then
                    conn.Open()
                End If
                Dim dr As SqlDataReader = comm.ExecuteReader
                Dim count As Integer = 0
                If dr.Read Then
                    For i As Integer = 0 To rowCount - 1
                        propertyList.Add(fn.CheckNull(dr(i)))
                    Next i
                End If
                dr.Close()
            Finally
                If (conn.State <> ConnectionState.Closed) Then
                    conn.Close()
                End If
                comm.Dispose()
            End Try
            Return propertyList
        End Function

        Public Function SelectList(ByVal sql As String, ByVal commandType As CommandType, ByVal parameters() As SqlParameter, ByVal datatable As DataTable) As DataTable
            Dim objs As List(Of Family) = New List(Of Family)
            Dim dt As DataTable = datatable
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            comm.CommandType = commandType
            If ((Not (parameters) Is Nothing) _
               AndAlso (parameters.Length > 0)) Then
                comm.Parameters.AddRange(parameters)
            End If
            Try
                If (conn.State <> ConnectionState.Open) Then
                    conn.Open()
                End If
                Dim dr As SqlDataReader = comm.ExecuteReader
                Dim row As DataRow
                Do While dr.Read
                    row = dt.NewRow
                    For i As Integer = 0 To dt.Columns.Count - 1
                        row(i) = dr(i)
                    Next i
                    dt.Rows.Add(row)
                Loop
                dr.Close()
            Catch ex As System.Exception
                Dim newEx As System.Exception = New System.Exception("From SelectList. See inner exception.", ex)
                Me.Errors.Add(newEx)
            Finally
                If (conn.State <> ConnectionState.Closed) Then
                    conn.Close()
                End If
                comm.Dispose()
            End Try
            Return dt
        End Function


        Public Function SelectList(ByVal sql As String, ByVal commandType As CommandType, ByVal parameters() As SqlParameter) As ArrayList
            Dim objs As List(Of Family) = New List(Of Family)
            Dim objList As New ArrayList
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            comm.CommandType = commandType
            If ((Not (parameters) Is Nothing) _
               AndAlso (parameters.Length > 0)) Then
                comm.Parameters.AddRange(parameters)
            End If
            Try
                If (conn.State <> ConnectionState.Open) Then
                    conn.Open()
                End If
                Dim dr As SqlDataReader = comm.ExecuteReader
                Do While dr.Read

                    Dim myImage As New Image

                    myImage.ImgId = dr(0)
                    myImage.UserId = dr(1)
                    myImage.Title = dr(2)
                    myImage.Caption = dr(3)
                    myImage.FilePath = dr(4)
                    myImage.Height = fn.CheckNull(dr(5))
                    myImage.Width = fn.CheckNull(dr(6))
                    myImage.IsPrivate = dr(7)

                    objList.Add(myImage)
                Loop
                dr.Close()
            Catch ex As System.Exception
                Throw ex
            Finally
                If (conn.State <> ConnectionState.Closed) Then
                    conn.Close()
                End If
                comm.Dispose()
            End Try
            Return objList
        End Function

        Public Function CreateEvent(ByVal hasAlbum As Boolean, ByVal userId As Integer, ByVal title As String, ByVal desc As String, ByVal [Date] As DateTime, ByVal location As String, ByVal address As String, ByVal city As String, ByVal state As String, ByVal zip As String, ByVal archive As Boolean, ByVal [Private] As Boolean) As Integer
            Dim eventId As Integer
            Dim connString As String = ConfigurationManager.ConnectionStrings("Henson Family").ConnectionString
            Dim conn As SqlConnection = New SqlConnection(connString)
            Dim comm As SqlCommand = New SqlCommand()

            conn.Open()

            comm.Connection = conn
            comm.CommandText = "sp_createEvent"
            comm.CommandType = Data.CommandType.StoredProcedure

            comm.Parameters.AddWithValue("@UserId", userId)
            comm.Parameters.AddWithValue("@AlbumId", 0)
            comm.Parameters.AddWithValue("@Title", title)
            comm.Parameters.AddWithValue("@Desc", fn.CheckNull(desc))
            comm.Parameters.AddWithValue("@Date", [Date])
            comm.Parameters.AddWithValue("@Location", fn.CheckNull(location))
            comm.Parameters.AddWithValue("@Address", fn.CheckNull(address))
            comm.Parameters.AddWithValue("@City", fn.CheckNull(city))
            comm.Parameters.AddWithValue("@State", fn.CheckNull(state))
            comm.Parameters.AddWithValue("@Zip", fn.CheckNull(zip))
            comm.Parameters.AddWithValue("@Archive", archive)
            comm.Parameters.AddWithValue("@Private", [Private])
            comm.Parameters.AddWithValue("@HasAlbum", hasAlbum)
            eventId = CInt(comm.ExecuteScalar())

            conn.Close()

            Return eventId
        End Function

        Public Function GetEvent(ByVal eventId As Integer) As DataTable
            Dim sql As String = "SELECT * FROM Events WHERE EventId = @EventId"
            Dim parameters As List(Of SqlParameter) = New List(Of SqlParameter)
            parameters.Add(New System.Data.SqlClient.SqlParameter("@EventId", eventId))
            Dim dt As DataTable = SelectList(sql, CommandType.Text, parameters.ToArray, dtEvents())


            Return dt
        End Function




    End Class
End Namespace

